/*****************************************************************************
 *   Copyright 2005 Tim A Wang
 *
 *   Licensed under the Apache License, Version 2.0 (the "License");
 *   you may not use this file except in compliance with the License.
 *   You may obtain a copy of the License at
 *
 *       http://www.apache.org/licenses/LICENSE-2.0
 *
 *   Unless required by applicable law or agreed to in writing, software
 *   distributed under the License is distributed on an "AS IS" BASIS,
 *   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *   See the License for the specific language governing permissions and
 *   limitations under the License.
 *
 ****************************************************************************/
package com.jb.web.bean.lookup;

import com.jb.framework.datasource.TableModel;

import com.jb.web.bean.BaseComponent;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

import java.util.ArrayList;
import java.util.Vector;

import com.jb.faces.component.UIComponentBase;

import javax.swing.ListModel;
import javax.swing.event.ListDataListener;


/**
 * DOCUMENT ME!
 *
 * @author $author$
 * @version $Revision$
 */
public class SqlTableObject extends BaseComponent implements ListModel {
    private ResultSetMetaData metaData;
    private String mSelectStmt = null;
    private TableModel _tableData = null;
    private Vector mBindVariable = new Vector();
    private Vector mColumns = new Vector();
    private Vector mMetaDatas = new Vector();
    private boolean mWhereAdded = false;
    private int mNumberOfColumns;

    public SqlTableObject(String newStmt) {
        if (!newStmt.equalsIgnoreCase(mSelectStmt)) {
            mSelectStmt = newStmt;
            _tableData = null;
            mBindVariable.removeAllElements();
        }
    }

    /**
     * DOCUMENT ME!
     *
     * @return DOCUMENT ME!
     */
    public Vector getColumns() {
        return mColumns;
    }

    /**
     * DOCUMENT ME!
     *
     * @param pRow DOCUMENT ME!
     *
     * @return DOCUMENT ME!
     */
    public Object getElementAt(int pRow) {
        return null;
    }

    /**
     * DOCUMENT ME!
     *
     * @return DOCUMENT ME!
     */
    public Vector getMetaDatas() {
        return mMetaDatas;
    }

    /**
     * DOCUMENT ME!
     *
     * @return return-value
     */
    public int getNumberOfCols() {
        return mNumberOfColumns;
    }

    /**
     * DOCUMENT ME!
     *
     * @param orderBy parameter
     *
     * @throws Exception exception
     */
    public void setOrderBy(String orderBy) throws Exception {
        if (mSelectStmt == null) {
            throw new Exception("Statment is required for SqlTableObject");
        }

        mSelectStmt = mSelectStmt + " order by " + orderBy;
    }

    /**
     * DOCUMENT ME!
     *
     * @return DOCUMENT ME!
     */
    public String getSelectStmt() {
        return mSelectStmt;
    }

    /**
     * DOCUMENT ME!
     *
     * @return DOCUMENT ME!
     */
    public int getSize() {
        return 1;
    }

    /**
     * DOCUMENT ME!
     *
     * @return DOCUMENT ME!
     */
    public TableModel getTableData() {
        return (_tableData);
    }

    /**
     * DOCUMENT ME!
     *
     * @param bindVar DOCUMENT ME!
     */
    public void addBindVariable(Object bindVar) {
        mBindVariable.addElement(bindVar);
    }

    /**
     * DOCUMENT ME!
     *
     * @param pListener DOCUMENT ME!
     */
    public void addListDataListener(ListDataListener pListener) {
    }

    /**
     * DOCUMENT ME!
     *
     * @param key parameter
     * @param value parameter
     * @param LoggericOperator parameter
     *
     * @throws Exception exception
     */
    public void addPredicate(String key, String value, String LoggericOperator)
        throws Exception {
        if (mSelectStmt == null) {
            throw new Exception("Statment is required for SqlTableObject");
        }

        if (value.length() > 0) {
            if (!mWhereAdded) {
                mSelectStmt = mSelectStmt + " where";
            } else {
                mSelectStmt = mSelectStmt + " " + LoggericOperator;
            }

            if (value.indexOf("%") < 0) {
                mSelectStmt = mSelectStmt + " " + key + " = '" + value + "'";
            } else {
                mSelectStmt = mSelectStmt + " " + key + " like '" + value + "'";
            }

            mWhereAdded = true;
        }
    }

    /**
     * DOCUMENT ME!
     *
     * @param pListener DOCUMENT ME!
     */
    public void removeListDataListener(ListDataListener pListener) {
    }

    /**
     * DOCUMENT ME!
     *
     * @param cnx DOCUMENT ME!
     *
     * @return DOCUMENT ME!
     *
     * @throws Exception DOCUMENT ME!
     */
    public boolean update(Connection cnx) throws Exception {
        if (mSelectStmt == null) {
            return false;
        }

        boolean bSuccess = false; // not sucess by default
        PreparedStatement sqlStmt = null;

        try {
            Connection tnxCnx = cnx;
            sqlStmt = tnxCnx.prepareStatement(mSelectStmt);
            bind(sqlStmt);
            sqlStmt.execute();

            ResultSet resultSet = sqlStmt.getResultSet();
            metaData = resultSet.getMetaData();
            mNumberOfColumns = metaData.getColumnCount();

            mColumns.removeAllElements();
            mMetaDatas.removeAllElements();

            for (int column = 0; column < mNumberOfColumns; column++) {
                String columnName = metaData.getColumnLabel(column + 1);
                mColumns.addElement(columnName);
                mMetaDatas.addElement(new TableColumn(metaData, column + 1));
            }

            ArrayList dbArray = new ArrayList();

            while (resultSet.next()) {
                Vector ht_temp = populate(resultSet);
                dbArray.add(ht_temp);
            }

            _tableData = new TableModel(dbArray);
            resultSet.close();
            sqlStmt.close();
            sqlStmt = null;
            resultSet = null;
            bSuccess = true;
        } catch (Exception sqlE) {
            try {
                if (sqlStmt != null) {
                    sqlStmt.close();
                    sqlStmt = null;
                }
            } catch (Exception e) {
            }

            throw sqlE;
        }

        return bSuccess;
    }

    private void bind(PreparedStatement sqlStmt) throws SQLException {
        if (sqlStmt == null) {
            return;
        }

        for (int i = 0; i < mBindVariable.size(); i++) {
            Object b = mBindVariable.elementAt(i);
            sqlStmt.setString(i, b.toString());
        }
    }

    private Vector populate(ResultSet resultSet) throws SQLException {
        Vector newRow = new Vector();

        for (int column = 0; column < mNumberOfColumns; column++) {
            if (resultSet.getObject(column + 1) != null) {
                newRow.addElement(resultSet.getObject(column + 1));
            } else {
                newRow.addElement(" ");
            }
        }

        return newRow;
    }
}
